package kr.oss.cho.flex;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import kr.oss.cho.mapper.SqlVeiwMapper;
import kr.oss.cho.util.SqlSessionFactoryUtil;
import kr.oss.cho.util.StringUtils;
import kr.oss.cho.vo.SessionBrowserVO;
import kr.oss.cho.vo.SessionLockBrowserVO;
import kr.oss.cho.vo.SqlInjectionVO;
import kr.oss.cho.vo.SqlMonitorVO;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;

import flex.messaging.MessageBroker;
import flex.messaging.messages.AsyncMessage;
import flex.messaging.util.UUIDUtils;


public class SqlSearch {
	
	private static final Log log = LogFactory.getLog(SqlSearch.class);
	
	private static SessionCountThread sessionCountThread;
	
	{
		started();
	}
	public void started() {
		System.out.println("sysStart");
		if( sessionCountThread == null ) {
			sessionCountThread = new SessionCountThread();
			sessionCountThread.start();
		} 
	}
	
	public void stopped() {
		sessionCountThread.running = false;
		sessionCountThread = null;
	}

	public static class SessionCountThread extends Thread {
		
		private static final Log log = LogFactory.getLog(SqlSearch.class);
		
		public boolean running = true;
		
		public void run() {
			MessageBroker msgBroker = MessageBroker.getMessageBroker(null);
			String clientID = UUIDUtils.createUUID(false);
			
			while( running ) {
				
				getSessionCount();
				
				try {
					Thread.sleep(1000);
				} catch(InterruptedException e) {
					
				}
			}
		}
		
		/**
		 * session Monitor
		 */
		public void getSessionCount(){
			
			MessageBroker msgBroker = MessageBroker.getMessageBroker(null);
			String clientID = UUIDUtils.createUUID(false);
			
			AsyncMessage msg = new AsyncMessage();
			msg.setDestination("sqlDataFeed");
			msg.setClientId(clientID);
			msg.setMessageId(UUIDUtils.createUUID(false));
			msg.setTimestamp(System.currentTimeMillis());
			
			SqlSessionFactory sqlMapper =  SqlSessionFactoryUtil.getInstance();
			
			SqlSession session = sqlMapper.openSession();
			
			Map<String, String> countVO = new HashMap<String, String>();
			try{
				SqlVeiwMapper mapper = session.getMapper(SqlVeiwMapper.class);
				
				int injectionCount = 0;
				injectionCount = StringUtils.checkSqlInjection(mapper.selectSqlInjectionList());
				
				Map<String, String> map = mapper.selectSessionCount();
				countVO.put("sessionCount", String.valueOf(map.get("CNT")));
				countVO.put("timeStamp", String.valueOf(map.get("TIMESTAMP")));
				countVO.put("sqlInjectionCount", injectionCount+"");
				
			}catch(Exception e){
				System.out.println(e.toString());
			}finally{
				session.close();
			}
			
			msg.setBody(countVO);
			
			if(log.isDebugEnabled()){
				log.debug("::Session Monitor [Session Info - Session Monitor]::");
				log.debug("Class: " + this.getClass().getName()+", Method Call: getSessionCount()");
				log.debug("AsyncMessage:" + msg);
			}
			
			msgBroker.routeMessageToService(msg, null);
		}
		
		
	}

	/**
	 *  method	: getSessionBrowser()
	 *  descript: 세션브라우져 새로고침
	 * @return
	 */
	public List<SessionBrowserVO> getSessionBrowser(String backgroundYn, String inactiveYn){
		SqlSessionFactory sqlMapper =  SqlSessionFactoryUtil.getInstance();
		
		SqlSession session = sqlMapper.openSession();
		
		ArrayList<SessionBrowserVO> list = new ArrayList<SessionBrowserVO>();
		try{
			SqlVeiwMapper mapper = session.getMapper(SqlVeiwMapper.class);
			
			Map<String, String> paramMap = new HashMap<String, String>();
			paramMap.put("ignoreBackground", backgroundYn);
			paramMap.put("ignoreInactive", inactiveYn);
			
			list = mapper.selectSessionBrowser(paramMap);
			
		}catch(Exception e){
			System.out.println(e.toString());
		}finally{
			session.close();
		}
		
		
		if(log.isDebugEnabled()){
			log.debug("::Session Browser [Session Info - Session Browser]::");
			log.debug("Class: " + this.getClass().getName()+", Method Call: getSessionBrowser()");
			log.debug("====== Param List start ======");
			log.debug("backgroundYn:" +backgroundYn);
			log.debug("inactiveYn:" +inactiveYn);
			log.debug("====== Param List end ======");
		}
		
		return list;
		
	}

	/**
	 *  method	: getSessionLockBrowser()
	 *  descript: Lock 세션브라우져 새로고침
	 * @return
	 */
	public List<SessionLockBrowserVO> getSessionLockBrowser(){
		SqlSessionFactory sqlMapper =  SqlSessionFactoryUtil.getInstance();
		
		SqlSession session = sqlMapper.openSession();
		
		ArrayList<SessionLockBrowserVO> list = new ArrayList<SessionLockBrowserVO>();
		try{
			SqlVeiwMapper mapper = session.getMapper(SqlVeiwMapper.class);
			
			list = mapper.selectSessionLockBrowser();
			
		}catch(Exception e){
			System.out.println(e.toString());
		}finally{
			session.close();
		}
		
		
		if(log.isDebugEnabled()){
			log.debug("::Lock Browser [Session Info - Lock Browser]::");
			log.debug("Class: " + this.getClass().getName()+", Method Call: getSessionLockBrowser()");
			log.debug("====== Param List start ======");
			log.debug("====== Param List end ======");
		}
		
		return list;
		
	}
	
	/**
	 *  method	: getCurrentSessionSqlText()
	 *  descript: 선택한 세션의 sql 가져오기
	 * @return
	 */
	public String getCurrentSessionSqlText(String sqlHashValue, String address){
		
		SqlSessionFactory sqlMapper =  SqlSessionFactoryUtil.getInstance();
		
		SqlSession session = sqlMapper.openSession();
		String sqlText = "";
		try{
			SqlVeiwMapper mapper = session.getMapper(SqlVeiwMapper.class);
			
			Map<String, String> paramMap = new HashMap<String, String>();
			paramMap.put("sqlHashValue", sqlHashValue);
			paramMap.put("address", address);
			
			sqlText = mapper.selectCurrentSessionSqlText(paramMap);
			
		}catch(Exception e){
			System.out.println(e.toString());
		}finally{
			session.close();
		}
		
		if(log.isDebugEnabled()){
			log.debug("::Session Browser [Session Info - Session Browser]::");
			log.debug("Class: " + this.getClass().getName()+", Method Call: getCurrentSessionSqlText()");
			log.debug("====== Param List start ======");
			log.debug("sqlHashValue:" +sqlHashValue);
			log.debug("address:" +address);
			log.debug("====== Param List end ======");
		}
		
		return sqlText;
	}
	/**
	 *  method	: controlCurrentSessionKill()
	 *  descript: 선택한 세션 KILL
	 * @return
	 */
	public String controlCurrentSessionKill(String sid, String serial){
		
		SqlSessionFactory sqlMapper =  SqlSessionFactoryUtil.getInstance();
		
		SqlSession session = sqlMapper.openSession();
		String result = "";
		try{
			SqlVeiwMapper mapper = session.getMapper(SqlVeiwMapper.class);
			
			Map<String, String> paramMap = new HashMap<String, String>();
			paramMap.put("sid", sid);
			paramMap.put("serial", serial);
			result = mapper.alterCurrentSessionKill(paramMap).toString();
			
		}catch(Exception e){
			result = StringUtils.getOracleErrorMessage(e.toString());
			log.error("에러내용:");
			log.error(result);
		}finally{
			session.close();
		}
		
		if(log.isDebugEnabled()){
			log.debug("::Session Browser [Session Info - Session Browser]::");
			log.debug("Class: " + this.getClass().getName()+", Method Call: controlCurrentSessionKill()");
			log.debug("====== Param List start ======");
			log.debug("sid:" +sid);
			log.debug("serial:" +serial);
			log.debug("====== Param List end ======");
			log.debug("result:" + result);
		}
		
		return result;
	}
	/**
	 *  method	: confirmSqlInjection()
	 *  descript: 선택한 sql Injection의 유무를 저장한다.
	 * @return
	 */
	public String confirmSqlInjection(String sqlId, String confirm){
		
		SqlSessionFactory sqlMapper =  SqlSessionFactoryUtil.getInstance();
		
		SqlSession session = sqlMapper.openSession();
		String result = "";
		try{
			SqlVeiwMapper mapper = session.getMapper(SqlVeiwMapper.class);
			
			Map<String, String> paramMap = new HashMap<String, String>();
			paramMap.put("sqlId", sqlId);
			paramMap.put("confirm", confirm);
			
			if(confirm.equals("Y")){
				result = mapper.updateConfirmSqlInjection(paramMap).toString();
			}else{
				result = mapper.deleteConfirmSqlInjection(paramMap).toString();
			}
			
			session.commit();
		}catch(Exception e){
			result = StringUtils.getOracleErrorMessage(e.toString());
			log.error("에러내용:");
			log.error(result);
			session.rollback();
		}finally{
			session.close();
		}
		
		if(log.isDebugEnabled()){
			log.debug("::Session Browser [Main - Sql Injection POPUP]::");
			log.debug("Class: " + this.getClass().getName()+", Method Call: confirmSqlInjection()");
			log.debug("====== Param List start ======");
			log.debug("sqlId:" +sqlId);
			log.debug("confirm:" +confirm);
			log.debug("====== Param List end ======");
			log.debug("result:" + result);
		}
		
		return result;
	}
	
	/**
	 *  method	: getSqlMonitor()
	 *  descript: sql 모니터링
	 * @return
	 */
	public List<SqlMonitorVO> getSqlMonitor(String S_bufferGet, String S_execution, String S_disreads, String S_SQL){

		
		SqlSessionFactory sqlMapper =  SqlSessionFactoryUtil.getInstance();
		
		SqlSession session = sqlMapper.openSession();
		
		ArrayList<SqlMonitorVO> list = new ArrayList<SqlMonitorVO>();
		try{
			SqlVeiwMapper mapper = session.getMapper(SqlVeiwMapper.class);
			
			Map<String, String> paramMap = new HashMap<String, String>();
			paramMap.put("buffer_gets", S_bufferGet);
			paramMap.put("executions", S_execution);
			paramMap.put("disk_reads", S_disreads);
			paramMap.put("sql", S_SQL);
			
			list = mapper.selectSqlMonitor(paramMap);
			
		}catch(Exception e){
			System.out.println(e.toString());
		}finally{
			session.close();
		}
		
		
		if(log.isDebugEnabled()){
			log.debug("::Sql 모니터링 조회::");
			log.debug("Class: " + this.getClass().getName()+", Method Call: getSqlMonitor()");
			log.debug("====== Param List start ======");
			log.debug("S_bufferGet:" +S_bufferGet);
			log.debug("S_execution:" +S_execution);
			log.debug("S_disreads:" +S_disreads);
			log.debug("S_SQL:" +S_SQL);
			log.debug("====== Param List end ======");
		}
		
		return list;
		
	}
	
	/**
	 *  method	: getSqlInjectionList()
	 *  descript: sql injection으로 감지된 sql 조회
	 * @return
	 */
	public List<SqlInjectionVO> getSqlInjectionDetectList(String yn, String S_sql_id, String S_schemaName, String S_attackCount, String S_rowCount, String S_attackDate){

		
		SqlSessionFactory sqlMapper =  SqlSessionFactoryUtil.getInstance();
		
		SqlSession session = sqlMapper.openSession();
		
		ArrayList<SqlInjectionVO> list = new ArrayList<SqlInjectionVO>();
		try{
			SqlVeiwMapper mapper = session.getMapper(SqlVeiwMapper.class);
			
			Map<String, String> paramMap = new HashMap<String, String>();
			paramMap.put("yn", yn);
			paramMap.put("S_sql_id", S_sql_id);
			paramMap.put("S_schemaName", S_schemaName);
			paramMap.put("S_attackCount", S_attackCount);
			paramMap.put("S_rowCount", S_rowCount);
			paramMap.put("S_attackDate", S_attackDate);
			
			list = mapper.selectSqlInjectionDetectList(paramMap);
			
		}catch(Exception e){
			System.out.println(e.toString());
		}finally{
			session.close();
		}
		
		
		if(log.isDebugEnabled()){
			log.debug("::sql injection으로 감지된 sql 조회::");
			log.debug("Class: " + this.getClass().getName()+", Method Call: getSqlInjectionList()");
			log.debug("====== Param List start ======");
			log.debug("yn:" +yn);
			log.debug("S_sql_id" +S_sql_id);
			log.debug("S_schemaName:" +S_schemaName);
			log.debug("S_attackCount" +S_attackCount);
			log.debug("S_rowCount" +S_rowCount);
			log.debug("S_attackDate" +S_attackDate);
			log.debug("====== Param List end ======");
		}
		
		return list;
		
	}
}
